package no.ntnu.fp.storage.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import no.ntnu.fp.handlers.exceptions.DatabaseException;
import no.ntnu.fp.handlers.exceptions.InvalidUserCredentialsException;
import no.ntnu.fp.model.Employee;
import no.ntnu.fp.server.ConnectionProvider;
import no.ntnu.fp.storage.EmployeeStorage;

public class EmployeeStorageImpl implements EmployeeStorage {

	@Override
	public Employee getEmployeeByCredentials(String username, String password) {
		Connection connection = ConnectionProvider.requestConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(
					"SELECT * FROM Employee WHERE username = ? AND password = ?");
			stmt.setString(1, username);
			stmt.setString(2, password);
			ResultSet resultSet = stmt.executeQuery();
			
			if (resultSet.next()) {
				Employee employee = getCurrentEmployee(resultSet);
				stmt.close();
				return employee;
			} else {
				stmt.close();
				throw new InvalidUserCredentialsException();
			}
			
		} catch (SQLException ex) {
			throw new DatabaseException("Unable to fetch employee from database", ex);
		} finally {
			ConnectionProvider.relinquishConnection();
		}
	}

	Employee getCurrentEmployee(ResultSet resultSet) throws SQLException {
		long employeeId = resultSet.getLong("employeeId");
		String employeeName = resultSet.getString("empName");
		String address = resultSet.getString("address");
		String phoneNumber = resultSet.getString("phoneNumber");
		String mail = resultSet.getString("mail");
		
		return new Employee(employeeId, employeeName, address, mail, phoneNumber);
	}

	Employee getEmployee(long id, Connection connection) throws SQLException {
		PreparedStatement stmt = connection.prepareStatement(
				"SELECT * FROM Employee " + 
				"WHERE employeeId = ?");
		
		stmt.setLong(1, id);
		
		ResultSet result = stmt.executeQuery();
		
		if (result.next()) {
			Employee employee = getCurrentEmployee(result);
			stmt.close();
			return employee;
		} else {
			stmt.close();
			throw new SQLException("No employee with id " + id);
		}
	}

	@Override
	public List<Employee> getAllEmployees() {
		Connection connection = ConnectionProvider.requestConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(
					"SELECT * FROM Employee");
			
			ResultSet resultSet = stmt.executeQuery();
			
			List<Employee> employees = new ArrayList<Employee>();
			while (resultSet.next()) {
				employees.add(getCurrentEmployee(resultSet));
			}
			
			stmt.close();
			
			return employees;
			
		} catch (SQLException e) {
			throw new DatabaseException("Unable to get all employees", e);
		} finally {
			ConnectionProvider.relinquishConnection();
		}
	}
}
